package org.jellylab.ojoverse.pico;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Enumeration;
import javax.servlet.http.HttpServletRequest;

/**
 * Static methods container, used to create Java and SQL code, to mapping and loading objects from HttpRequests, to setting PreparedStatements and Resultsets from objects  
 */
public class PicoPOJOManager {

    /**
     * Set object fields using same-name request parameters
     * @param request HttpServletRequest
     * @param data Empty object to load
     * @return Loaded object
     * @throws IllegalArgumentException
     * @throws IllegalAccessException
     */
    public static Object loadObjectFromRequest(HttpServletRequest request, Object data) {
        Enumeration enume = request.getParameterNames();
        Field[] dataFields = data.getClass().getFields();

        int errMaxSize = 10;
        String[][] errori = new String[errMaxSize][2];
        int errCount = 0;
        while (enume.hasMoreElements()) {
            String name = (String) enume.nextElement();
            String value = request.getParameter(name);
            for (int idf = 0; idf < dataFields.length; idf++) {
                Field field = dataFields[idf];
                if (field.getName().equals(name)) {
                    try {
                        field.setAccessible(true);
                        String type = field.getType().getName();
                        if (type.equals(String.class.getName())) {
                            field.set(data, value);
                        } else if (type.equals("int")) {
                            int intVal = new Integer(value).intValue();
                            field.setInt(data, intVal);
                        } else if (type.equals("double")) {
                            double dblVal = new Double(value).doubleValue();
                            field.setDouble(data, dblVal);
                        }
                    } catch (Exception exc) {
                        if (errCount < errMaxSize) {
                            errori[errCount][0] = name;
                            errori[errCount][1] = value;
                        }
                        errCount++;
                    }
                }
            }
        }
        if (errCount > 0) {
            request.setAttribute("erroreLoad", errori);
        }
        return data;
    }

    /**
     * Create the SQL WHERE clause from a POJO object
     * if field value is not null add a SQL block: "FIELDNAME = ?"
     * @param data
     * @return SQL string
     */
    public static String createWhereClause(Object data) {
        Field[] fields;
        if (data instanceof DataPOJO) {
            fields = ((DataPOJO) data).getFields();
        } else {
            fields = data.getClass().getFields();
        }

        if (fields == null) {
            return "";
        }

        int fieldLen = fields.length;

        StringBuffer where = new StringBuffer(1000);
        // loop sui campi della StrutturaDati
        for (int idf = 0; idf < fieldLen; idf++) {
            Field field = fields[idf];
            field.setAccessible(true);
            String name = field.getName();
            String type = field.getType().getName();

            StringBuffer whereField = new StringBuffer(50);
            // se di tipo stringa
            if (type.equals(String.class.getName())) {
                // ottengo il valore del campo, in caso di errore o campo nullo proseguo il ciclo for
                Object value = null;
                try {
                    value = field.get(data);
                } catch (Exception exc) {
                    continue;
                }
                if (value == null) {
                    continue;
                }
                String strVal = value.toString();
                if (!strVal.equals("")) {
                    whereField.append(name.toUpperCase());
                    whereField.append(" = ?");
                }
            } else if (type.equals("int")) {
                int intVal = 0;
                try {
                    intVal = field.getInt(data);
                } catch (Exception exc) {
                    continue;
                }
                if (intVal != 0) {
                    whereField.append(name.toUpperCase());
                    whereField.append(" = ?");
                }
            } else if (type.equals("double")) {
                double dblVal = 0;
                try {
                    dblVal = field.getDouble(data);
                } catch (Exception exc) {
                    continue;
                }
                if (dblVal != 0) {
                    whereField.append(name.toUpperCase());
                    whereField.append(" = ?");
                }
            }

            if (whereField.length() > 0) {
                if (where.length() > 0) {
                    where.append(" AND ");
                }
                where.append(whereField);
            }
        }

        return where.toString();
    }

    /**
     * Used with previous @see createWhereClause
     * Load a PreparedStatement using a DataPOJO:
     * for each not null field set the PreparedStatement field value
     * @param data
     * @return loaded PreparedStatement
     */
    public static PreparedStatement prepareStatement(PreparedStatement prstm, Object data) throws SQLException {
        Field[] fields;
        if (data instanceof DataPOJO) {
            fields = ((DataPOJO) data).getFields();
        } else {
            fields = data.getClass().getFields();
        }

        int fieldLen = fields.length;
        int pos = 1;
        for (int idf = 0; idf < fieldLen; idf++) {
            Field field = fields[idf];
            field.setAccessible(true);
            String type = field.getType().getName();

            if (type.equals(String.class.getName())) {
                Object value = null;
                try {
                    value = field.get(data);
                } catch (Exception exc) {
                    continue;
                }
                if (value == null) {
                    continue;
                }
                String strVal = value.toString();
                if (!strVal.equals("")) {
                    prstm.setString(pos++, strVal);
                }
            } else if (type.equals("int")) {
                int intVal = 0;
                try {
                    intVal = field.getInt(data);
                } catch (Exception exc) {
                    continue;
                }
                if (intVal != 0) {
                    prstm.setInt(pos++, intVal);
                }
            } else if (type.equals("double")) {
                double dblVal = 0;
                try {
                    dblVal = field.getDouble(data);
                } catch (Exception exc) {
                    continue;
                }
                if (dblVal != 0) {
                    prstm.setDouble(pos++, dblVal);
                }
            }
        }

        return prstm;
    }

    public static Object loadObjectFromResultSetRow(ResultSet rset, Object data) {
        Field[] fields;
        if (data instanceof DataPOJO) {
            fields = ((DataPOJO) data).getFields();
        } else {
            fields = data.getClass().getFields();
        }

        int fieldLen = fields.length;
        for (int idf = 0; idf < fieldLen; idf++) {
            Field field = fields[idf];
            String fieldName = field.getName().toUpperCase();
            field.setAccessible(true);
            String type = field.getType().getName();

            if (type.equals(String.class.getName())) {
                try {
                    String value = rset.getString(fieldName);
                    field.set(data, value);
                } catch (Exception exc) {
                    continue;
                }
            } else if (type.equals("int")) {
                try {
                    int value = rset.getInt(fieldName);
                    field.setInt(data, value);
                } catch (Exception exc) {
                    continue;
                }
            } else if (type.equals("double")) {
                try {
                    double value = rset.getDouble(fieldName);
                    field.setDouble(data, value);
                } catch (Exception exc) {
                    continue;
                }
            }
        }

        return data;
    }

    /**
     * A work in progress code generator
     * @param cnn
     * @param dataPOJOname Name for the generated DataPOJO
     * @param innerVariableName Name of DataPOJO instance variable
     * @return A string with generated code
     * @throws SQLException
     */
    public static String createCodeFromQuery(Connection cnn, String query, String dataPOJOname, String innerVariableName) throws SQLException {
        Statement stmt = cnn.createStatement();
        ResultSet rset = stmt.executeQuery(query + " WHERE 1=0");

        ResultSetMetaData metadata = rset.getMetaData();
        int numColumns = metadata.getColumnCount();
        StringBuffer sbDeclareStruct = new StringBuffer(1000);
        StringBuffer sbReadStruct = new StringBuffer(1000);
        StringBuffer sbWriteStruct = new StringBuffer(1000);
        StringBuffer sbSelect = new StringBuffer(1000);
        StringBuffer sbInsert = new StringBuffer(1000);
        StringBuffer sbUpdate = new StringBuffer(1000);

        sbDeclareStruct.append("\n\nCodice generato automaticamente:\n");
        sbDeclareStruct.append("\npublic class ");
        sbDeclareStruct.append(dataPOJOname);
        sbDeclareStruct.append(" extends StrutturaDati {\n");
        sbSelect.append("\nSELECT ");
        sbInsert.append("\nINSERT INTO TABELLA (");
        sbUpdate.append("\nUPDATE TABELLA SET ");
        for (int idc = 1; idc < (numColumns + 1); idc++) {
            String columnName = metadata.getColumnName(idc);
            String columnTypeName = metadata.getColumnTypeName(idc);
            /*
            String columnClassName = metadata.getColumnClassName(idc);
            String columnTableName = metadata.getTableName(idc);
            int columnPrecision = metadata.getPrecision(idc);
            int columnScale = metadata.getScale(idc);
             */
            sbSelect.append(columnName);
            sbInsert.append(columnName);
            sbUpdate.append(columnName);
            sbUpdate.append(" = ?");
            if (idc < numColumns) {
                sbSelect.append(", ");
                sbInsert.append(", ");
                sbUpdate.append(", ");
            }
            sbDeclareStruct.append("\tpublic");
            sbReadStruct.append(innerVariableName);
            sbReadStruct.append(".");
            sbReadStruct.append(columnName.toLowerCase());
            sbReadStruct.append(" = ");
            sbWriteStruct.append("\nprstmt.");
            if (columnTypeName.equalsIgnoreCase("VARCHAR") || columnTypeName.equalsIgnoreCase("CHAR")) {
                sbDeclareStruct.append(" String ");
                sbReadStruct.append("Misc.nvl(rset.getString(\"");
                sbWriteStruct.append("setString(pos++, ");
            } else if (columnTypeName.equalsIgnoreCase("INT")) {
                sbDeclareStruct.append(" int ");
                sbReadStruct.append("rset.getInt(\"");
                sbWriteStruct.append("setInt(pos++, ");
            } else if (columnTypeName.equalsIgnoreCase("NUMERIC")) {
                sbDeclareStruct.append(" double ");
                sbReadStruct.append("rset.getDouble(\"");
                sbWriteStruct.append("setDouble(pos++, ");
            }
            sbDeclareStruct.append(columnName.toLowerCase());
            sbDeclareStruct.append("=");
            sbReadStruct.append(columnName.toUpperCase());
            sbWriteStruct.append(innerVariableName);
            sbWriteStruct.append(".");
            sbWriteStruct.append(columnName.toLowerCase());
            sbWriteStruct.append(");");
            if (columnTypeName.equalsIgnoreCase("VARCHAR") || columnTypeName.equalsIgnoreCase("CHAR")) {
                sbDeclareStruct.append(" \"\"");
                sbReadStruct.append("\"), \"\");\n");
            } else if (columnTypeName.equalsIgnoreCase("INT")) {
                sbDeclareStruct.append(" 0");
                sbReadStruct.append("\");\n");
            } else if (columnTypeName.equalsIgnoreCase("NUMERIC")) {
                sbDeclareStruct.append(" 0");
                sbReadStruct.append("\");\n");
            }
            sbDeclareStruct.append(";\n");
        }
        sbDeclareStruct.append("}\n");
        // INSERT / UPDATE
        sbInsert.append(")\n");
        sbInsert.append("VALUES (");
        for (int idc = 1; idc < (numColumns + 1); idc++) {
            sbInsert.append("?");
            if (idc < numColumns) {
                sbInsert.append(", ");
            }
        }
        sbInsert.append(");\n");
        //SELECT
        sbSelect.append(" FROM TABELLA;\n");

        if (rset != null) {
            rset.close();
        }
        if (stmt != null) {
            stmt.close();
        }

        sbDeclareStruct.append(sbSelect).append(sbReadStruct);
        sbDeclareStruct.append(sbInsert).append(sbUpdate).append(sbWriteStruct);

        return sbDeclareStruct.toString();
    }

    public static void close(ResultSet rset, Statement stmt) {
        try {
            if (rset != null) {
                rset.close();
            }
            if (stmt != null) {
                stmt.close();
            }
        } catch (SQLException sqle) {
        }
    }

    /**
     * A simple POJO abstract base class with simple utilities used to obtains fields array, class name and a human-readable toString value
     * Isn't needed to extends this class, only useful
     */
    public static abstract class DataPOJO {

        private transient Field[] fields = this.getClass().getFields();
        private transient String name = this.getClass().getName();

        public Field[] getFields() {
            return fields;
        }

        public String getSingleName() {
            return name.substring(name.indexOf("$") + 1);
        }

        public String toString() {
            String strVal = "[" + getSingleName() + "]";
            for (int idf = 0; idf < fields.length; idf++) {
                Field field = fields[idf];
                field.setAccessible(true);
                if (idf > 0) {
                    strVal += ", ";
                }
                strVal += field.getName() + ":";
                try {
                    strVal += field.get(this);
                } catch (Exception exc) {
                    strVal += "{error}";
                }
            }
            return strVal;
        }
    }
}
